<?php

# PMB Online
# Aplikasi PMB Online untuk Pendaftaran Calon Mahasiswa di PT/PTS Indonesia
# 
# version 1.0
# class csiswa
# file models/csiswa.php
# created Jan 22, 2013 3:20:42 PM
# 
# (c)2012, arif.laksito@gmail.com

if (!defined('BASEPATH'))
    exit('No direct script access allowed');

class Csiswa extends SI_Model {

    var $data = array();

    public function __construct() {
        parent::__construct();
    }

    public function get_nodaf($tha) {
        $pre = substr($tha, 2);
        $str = "Select SUBSTRING(Max(nodaf)+1,3,5) as nodaf from csiswa
            Where nodaf like '" . $pre . "%' ";
        $que = $this->db->query($str);
        $no = $que->row_array();        
        $nodaf = ($no['nodaf']!=NULL)?$no['nodaf']:'00001';

        return $pre . $nodaf;
    }

    public function insert($data = array()) {
        $rules = array(            
            array('field' => 'id_jurusan', 'label' => 'Jurusan', 'rules' => 'numeric|trim'),
            array('field' => 'nama_lengkap', 'label' => 'Nama Lengkap', 'rules' => 'required|trim'),  
            array('field' => 'tempat_lahir', 'label' => 'Tempat Lahir', 'rules' => 'required|trim'),  
            array('field' => 'tgl_lahir', 'label' => 'Tgl Lahir', 'rules' => 'required|trim'),  
            array('field' => 'jkel', 'label' => 'Jenis Kelamin', 'rules' => 'numeric|trim'),
            array('field' => 'agama', 'label' => 'Agama', 'rules' => 'numeric|trim'),
            array('field' => 'telp', 'label' => 'No.Telp/HP', 'rules' => 'numeric|trim'),
            array('field' => 'email', 'label' => 'Alamat Email', 'rules' => 'valid_email|trim'),
            array('field' => 'th_lulus', 'label' => 'Tahun Lulus', 'rules' => 'numeric|trim'),
            array('field' => 'nem', 'label' => 'NEM/IPK', 'rules' => 'numeric|trim'),
            array('field' => 'status_nikah', 'label' => 'Status Nikah', 'rules' => 'numeric|trim'),
            array('field' => 'kodepos_asal', 'label' => 'Kodepos Asal', 'rules' => 'numeric|trim'),
            array('field' => 'kodepos', 'label' => 'Kodepos Tempat Tinggal', 'rules' => 'numeric|trim'),
            array('field' => 'kodepos_ortu', 'label' => 'Kodepos Orangtua', 'rules' => 'numeric|trim'),
            array('field' => 'telp_ortu', 'label' => 'No.Telp/HP Orangtua', 'rules' => 'numeric|trim'),
        );

        if (!$this->set_field($rules)) {
            $this->umsg = $this->msg;
            return false;
        }

        
        $this->load->model('authlogin');
        $usr = $this->authlogin->get_cookies();
        
        $data['cs_daftar'] = $usr['name'];
        $data['nama_lengkap'] = strtoupper($data['nama_lengkap']);
        $data['tgl_daftar'] = date('Y-m-d');
        if ($this->db->insert('csiswa', $data)) {
            $this->umsg = 'Data pendaftar berhasil ditambahkan';
            return true;
        } else {
            $this->umsg = 'Data pendaftar gagal ditambahkan';
            return false;
        }
    }
    
    public function edit($nodaf, $data = array()){
        $rules = array(            
            array('field' => 'id_jurusan', 'label' => 'Jurusan', 'rules' => 'numeric|trim'),
            array('field' => 'nama_lengkap', 'label' => 'Nama Lengkap', 'rules' => 'required|trim'),  
            array('field' => 'tempat_lahir', 'label' => 'Tempat Lahir', 'rules' => 'required|trim'),  
            array('field' => 'tgl_lahir', 'label' => 'Tgl Lahir', 'rules' => 'required|trim'),  
            array('field' => 'jkel', 'label' => 'Jenis Kelamin', 'rules' => 'numeric|trim'),
            array('field' => 'agama', 'label' => 'Agama', 'rules' => 'numeric|trim'),
            array('field' => 'telp', 'label' => 'No.Telp/HP', 'rules' => 'numeric|trim'),
            array('field' => 'email', 'label' => 'Alamat Email', 'rules' => 'valid_email|trim'),
            array('field' => 'th_lulus', 'label' => 'Tahun Lulus', 'rules' => 'numeric|trim'),
            array('field' => 'nem', 'label' => 'NEM/IPK', 'rules' => 'numeric|trim'),
            array('field' => 'status_nikah', 'label' => 'Status Nikah', 'rules' => 'numeric|trim'),
            array('field' => 'kodepos_asal', 'label' => 'Kodepos Asal', 'rules' => 'numeric|trim'),
            array('field' => 'kodepos', 'label' => 'Kodepos Tempat Tinggal', 'rules' => 'numeric|trim'),
            array('field' => 'kodepos_ortu', 'label' => 'Kodepos Orangtua', 'rules' => 'numeric|trim'),
            array('field' => 'telp_ortu', 'label' => 'No.Telp/HP Orangtua', 'rules' => 'numeric|trim'),
        );

        if (!$this->set_field($rules)) {
            $this->umsg = $this->msg;
            return false;
        }
        
        $this->load->model('authlogin');
        $usr = $this->authlogin->get_cookies();
        
        $data['cs_edit'] = $usr['name'];
        $data['nama_lengkap'] = strtoupper($data['nama_lengkap']);
        
        $this->db->where('nodaf', $nodaf);
        if ($this->db->update('csiswa', $data)) {
            $this->umsg = 'Data Pendaftar berhasil diedit';
            return true;
        } else {
            $this->umsg = 'Data Pendaftar gagal diedit';
            return false;
        }
    }
    
    public function get($nodaf){
        $this->db->join('gelombang','gelombang.id_gel=csiswa.id_gel','left');
        $this->db->where('nodaf', $nodaf);
        $que = $this->db->get('csiswa');
        
        return $que->row_array();
    }
    
    public function get_all(){
        $this->db->join('gelombang','gelombang.id_gel=csiswa.id_gel','left');  
        //$this->db->join('tha_jur','gelombang.id_tha_jur = tha_jur.id_tha_jur','left');
        $this->db->join('tha','tha.id_tha = gelombang.id_tha','left');
        $this->db->where('tha.status',1);
        $que = $this->db->get('csiswa');
        
        return $que->result_array();
    }
    
    public function get_lulus(){
        $this->db->where('csiswa.cs_lulus is not null');
        $this->db->where('tha.status',1);
        $this->db->join('gelombang','gelombang.id_gel=csiswa.id_gel','left');        
        $this->db->join('jurusan','jurusan.id_jurusan=csiswa.id_jurusan','left');
        $this->db->join('tha','tha.id_tha = gelombang.id_tha','left');
        $que = $this->db->get('csiswa');
        
        return $que->result_array();
    }
    
    public function get_blm_lulus(){
        $this->db->where('biaya_daftar',1);
        $this->db->where('kelengkapan',1);
        $this->db->where('csiswa.id_jurusan is null');
        $this->db->where('tha.status',1);
        $this->db->where('cs_lulus is NULL');
        $this->db->join('gelombang','gelombang.id_gel=csiswa.id_gel','left');  
        $this->db->join('tha','tha.id_tha = gelombang.id_tha','left');
        //$this->db->join('tha_jur','gelombang.id_tha_jur = tha_jur.id_tha_jur','left');
        //$this->db->join('tha','tha_jur.id_tha = tha.id_tha','left');        
        $que = $this->db->get('csiswa');
        
        return $que->result_array();
    }
    
    public function set_lulus($nodaf, $id_jur){
        
        $this->load->model(array('biaya','authlogin'));
        
        $mhs = $this->get($nodaf);
        $bea = $this->biaya->get_beasiswa($mhs['id_gel'], $id_jur);
        $usr = $this->authlogin->get_cookies();
        
        $data['cs_lulus'] = $usr['name'];
        $data['beasiswa'] = $bea['beasiswa'];
        $data['tgl_lulus'] = date('Y-m-d');
        $data['id_jurusan'] = $id_jur;
        
        $this->db->where('nodaf', $nodaf);
        if ($this->db->update('csiswa', $data)) 
            return true;
        else return false;
        
    }
    
    public function set_nlulus($nodaf){
        $this->load->model('authlogin');
        
        $usr = $this->authlogin->get_cookies();
        $data['cs_lulus'] = $usr['name'];
        $data['beasiswa'] = '0';
        $data['tgl_lulus'] = date('Y-m-d');        
        
        $this->db->where('nodaf', $nodaf);
        if ($this->db->update('csiswa', $data)) 
            return true;
        else return false;
    }


    public function edit_lulus($nodaf,$data = array()){
        $rules = array(            
            array('field' => 'beasiswa', 'label' => 'Beasiswa', 'rules' => 'numeric|trim')
        );    
        
        if (!$this->set_field($rules)) {
            $this->umsg = $this->msg;
            return false;
        }
        
        $this->load->model('authlogin');
        $usr = $this->authlogin->get_cookies();
        
        $data['cs_edit'] = $usr['name'];
        $this->db->where('nodaf', $nodaf);
        if ($this->db->update('csiswa', $data)) {
            $this->umsg = 'Data Pendaftar berhasil diedit';
            return true;
        } else {
            $this->umsg = 'Data Pendaftar gagal diedit';
            return false;
        }
        
    }
    
    public function delete($nodaf){
        $this->db->where('nodaf',$nodaf);
        if($this->db->delete('csiswa')){            
            return true;
        }else{
            return false;
        }
    }
    
    public function check_lulus($nodaf, $tgl){
        $this->db->where('nodaf', $nodaf);
        $this->db->where('tgl_lahir',$tgl);        
        $que = $this->db->get('csiswa');
        
        return $que->row_array();
    }
    
    public function count_siswa($gel){
        $out = array();
        
        $this->db->select('COUNT(*) AS ls');
        $this->db->where('id_jurusan IS NULL');
        $this->db->where('cs_lulus IS NULL');
        $this->db->where('id_gel',$gel);
        $que = $this->db->get('csiswa');
        $rst = $que->row_array();
        $out['nlulus'] = $rst['ls'];
        
        $this->db->select('COUNT(*) AS ts');
        $this->db->where('id_jurusan IS NULL');
        $this->db->where('cs_lulus IS NOT NULL');
        $this->db->where('id_gel',$gel);
        $que = $this->db->get('csiswa');
        $rst = $que->row_array();
        $out['tlulus'] = $rst['ts'];
        
        $this->db->select('COUNT(*) AS nl');
        $this->db->where('id_jurusan IS NOT NULL');
        $this->db->where('id_gel',$gel);
        $que = $this->db->get('csiswa');
        $rst = $que->row_array();
        $out['lulus'] = $rst['nl'];
        
        
        
        return $out;
    }
    
    public function count_ol($gel){
        $out = array();
        
        $this->db->select('COUNT(*) AS jml');
        $this->db->where('daftar_ol',1);
        $this->db->where('id_gel',$gel);
        $que = $this->db->get('csiswa');
        $rst = $que->row_array();
        $out['ol'] = $rst['jml'];
        
        $this->db->select('COUNT(*) AS jml');
        $this->db->where('daftar_ol',0);
        $this->db->where('id_gel',$gel);
        $que = $this->db->get('csiswa');
        $rst = $que->row_array();
        $out['off'] = $rst['jml'];
        
        return $out;
    }
    
    public function count_siswa_gel(){
        $this->load->model('gelombang');
        $gel = $this->gelombang->get_gelombang();
        $out = array();
        foreach($gel as $g){            
            $out[$g['id_gel']] = $this->count_siswa($g['id_gel']);
            $out[$g['id_gel']]['gel'] = $g['nama_gel'];
        }
        
        return $out;
    }
    
    public function count_siswa_ol(){
        $this->load->model('gelombang');
        $gel = $this->gelombang->get_gelombang();
        $out = array();
        foreach($gel as $g){            
            $out[$g['id_gel']] = $this->count_ol($g['id_gel']);
            $out[$g['id_gel']]['gel'] = $g['nama_gel'];
        }
        
        return $out;
    }
    
    public function count_asal(){
        $str = "SELECT sekolah_asal, COUNT(*) AS jml 
            FROM csiswa
            WHERE sekolah_asal IS NOT NULL AND sekolah_asal<>''
            GROUP BY sekolah_asal
            LIMIT 0,10";
        $que = $this->db->query($str);
        return $que->result_array();
    }
    
    public function count_info(){
        $out = array();
        $str = "SELECT COUNT(*) AS jml FROM csiswa
            WHERE info LIKE '%Brosur%'";
        $que = $this->db->query($str);
        $rs = $que->row_array();
        $out['Brosur'] = $rs['jml'];
        
        $str = "SELECT COUNT(*) AS jml FROM csiswa
            WHERE info LIKE '%Internet%'";
        $que = $this->db->query($str);
        $rs = $que->row_array();
        $out['Internet'] = $rs['jml'];
        
        $str = "SELECT COUNT(*) AS jml FROM csiswa
            WHERE info LIKE '%Teman/Saudara%'";
        $que = $this->db->query($str);
        $rs = $que->row_array();
        $out['Teman/Sdr'] = $rs['jml'];
        
        $str = "SELECT COUNT(*) AS jml FROM csiswa
            WHERE info LIKE '%Lainnya%'";
        $que = $this->db->query($str);
        $rs = $que->row_array();
        $out['Lain2'] = $rs['jml'];
        
        return $out;
    }
}